package com.example.unique.database

import android.annotation.SuppressLint
import android.content.ContentValues
import android.content.Context
import android.database.Cursor
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteOpenHelper

class DatabaseTaskCompletion private constructor(context: Context) : SQLiteOpenHelper(context, "TaskCompletion.db", null, 1) {

    companion object {
        @Volatile
        private var INSTANCE: DatabaseTaskCompletion? = null

        fun getInstance(context: Context): DatabaseTaskCompletion {
            return INSTANCE ?: synchronized(this) {
                INSTANCE ?: DatabaseTaskCompletion(context.applicationContext).also { INSTANCE = it }
            }
        }
    }

    override fun onCreate(db: SQLiteDatabase) {
        // 创建 TaskCompletion 表
        val createCompletionTable = """
            CREATE TABLE TaskCompletion (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                club TEXT NOT NULL,       -- 社团名
                taskName TEXT NOT NULL,   -- 任务名
                isCompleted INTEGER DEFAULT 0, 
                completorSno TEXT NOT NULL-- 学号
            )
        """
        db.execSQL(createCompletionTable)
    }

    override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
        db.execSQL("DROP TABLE IF EXISTS TaskCompletion")
        onCreate(db)
    }

    // 插入任务完成记录
    @SuppressLint("Range")
    fun insertTaskCompletionForClub(club: String, taskName: String, userDatabase: DatabaseHelper) {
        val db = this.writableDatabase

        // 查询该社团的所有成员
        val userCursor = userDatabase.getUserByClub(club)

        // 遍历每个成员，将其插入 TaskCompletion 表中
        if (userCursor.moveToFirst()) {
            do {
                val sno = userCursor.getString(userCursor.getColumnIndex("sno"))
                val contentValues = ContentValues()
                contentValues.put("club", club)
                contentValues.put("taskName", taskName)
                contentValues.put("completorSno", sno)
                db.insert("TaskCompletion", null, contentValues)
            } while (userCursor.moveToNext())
        }
        userCursor.close()
    }

    // 查询某任务的完成者列表
    fun getTaskCompletions(club:String,taskName: String): Cursor {
        val db = this.readableDatabase
        return db.rawQuery("SELECT * FROM TaskCompletion WHERE club = ? AND taskName = ?", arrayOf(club,taskName))
    }

    // 查询某社团下所有任务的完成记录
    fun getCompletionsByClub(club: String): Cursor {
        val db = this.readableDatabase
        return db.rawQuery("SELECT * FROM TaskCompletion WHERE club = ?", arrayOf(club))
    }

    // 检查某任务是否有完成记录
    fun isTaskCompleted(taskName: String, completorSno: String): Boolean {
        val db = this.readableDatabase
        val cursor = db.rawQuery(
            "SELECT * FROM TaskCompletion WHERE taskName = ? AND completorSno = ?",
            arrayOf(taskName, completorSno)
        )
        val exists = cursor.count > 0
        cursor.close()
        return exists
    }

    // 删除特定任务完成记录
    fun deleteCompletion(taskName: String, completorSno: String): Int {
        val db = this.writableDatabase
        return db.delete(
            "TaskCompletion",
            "taskName = ? AND completorSno = ?",
            arrayOf(taskName, completorSno)
        )
    }

    // 删除某任务的所有完成记录
    fun deleteAllCompletionsForTask(taskName: String): Int {
        val db = this.writableDatabase
        return db.delete("TaskCompletion", "taskName = ?", arrayOf(taskName))
    }

    fun updateIsCompleted(club: String,taskName: String,sno:String,isCompleted:Int){
        val db = this.writableDatabase
        val contentValues = ContentValues()
        contentValues.put("isCompleted", isCompleted)
        db.update("TaskCompletion", contentValues, "club = ? AND taskName=? AND sno = ?", arrayOf(club,taskName,sno))
    }

}
